package mapper;

import entity.BusEntity;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author HuangJH
 * @create 2023-11-30-20:10
 */
public class BusMapper {
    /**
     * 增加班车
     * @param busEntity
     * @return
     */
    public boolean addBus(BusEntity busEntity) {
        List<BusEntity> busList = getAllBy(null, busEntity.getBusName());
        if(null != busList && busList.size() > 0) {
            System.out.println("该班车已存在：" + busEntity.getBusName());
            return false;
        }
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        int successNum = 0;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "insert into bus(bus_name, departure_station, terminal) values(?, ?, ?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, busEntity.getBusName());
            preparedStatement.setString(2, busEntity.getDepartureStation());
            preparedStatement.setString(3, busEntity.getTerminal());
            successNum = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println("添加班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        if (successNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 查询班车
     * @param id
     * @param busName
     * @return
     */
    public List<BusEntity> getAllBy(Integer id, String busName) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        List<BusEntity> busEntities = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            if(id != null) {
                String sql = "select * from bus where id = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, id);
            } else if(null != busName && "" != busName) {
                String sql = "select * from bus where bus_name = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, busName);
            }

            set = preparedStatement.executeQuery();
            while (set.next()) {
                BusEntity busEntity = new BusEntity();
                busEntity.setId(set.getInt("id"));
                busEntity.setBusName(set.getString("bus_name"));
                busEntity.setDepartureStation(set.getString("departure_station"));
                busEntity.setTerminal(set.getString("terminal"));
                busEntities.add(busEntity);
            }
        } catch (Exception e) {
            System.out.println("查询所有班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        return busEntities;
    }

    /**
     * 查询所有班车
     * @return
     */
    public List<BusEntity> getAllBus() {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        List<BusEntity> busEntities = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select * from bus;";
            preparedStatement = connection.prepareStatement(sql);
            set = preparedStatement.executeQuery();
            while (set.next()) {
                BusEntity busEntity = new BusEntity();
                busEntity.setId(set.getInt("id"));
                busEntity.setBusName(set.getString("bus_name"));
                busEntity.setDepartureStation(set.getString("departure_station"));
                busEntity.setTerminal(set.getString("terminal"));
                busEntities.add(busEntity);
            }
        } catch (Exception e) {
            System.out.println("查询所有班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        return busEntities;
    }

    /**
     * 修改班车信息
     * @param update
     * @param updateSelect
     * @return
     */
    public boolean updateBus(BusEntity update, int updateSelect) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        int successNum = 0;
        try {
            String sql = null;
            connection = JDBCUtils.getConnection();
            switch (updateSelect) {
                case 1:
                    sql = "UPDATE bus SET bus_name=?  WHERE id=?;";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, update.getBusName());
                    preparedStatement.setInt(2, update.getId());
                    break;
                case 2:
                    sql = "UPDATE bus SET departure_station=? WHERE id=?;";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, update.getDepartureStation());
                    preparedStatement.setInt(2, update.getId());
                    break;
                case 3:
                    sql = "UPDATE bus SET terminal=? WHERE id=?;";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, update.getTerminal());
                    preparedStatement.setInt(2, update.getId());
                    break;
                case 4:
                    sql = "UPDATE bus SET bus_name=?, departure_station=?, terminal=? WHERE id=?;";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, update.getBusName());
                    preparedStatement.setString(2, update.getDepartureStation());
                    preparedStatement.setString(3, update.getTerminal());
                    preparedStatement.setInt(4, update.getId());
                    break;
                default:
                    break;
            }
            successNum = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println("修改班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        if (successNum > 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 删除班车
     * @param deleteId
     * @return
     */
    public boolean deleteBus(int deleteId) {
        //声明JDBC 中需要用到的几个变量
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        int successNum = 0;
        try {
            String sql = null;
            connection = JDBCUtils.getConnection();
            sql = "delete from bus WHERE id=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, deleteId);
            successNum = preparedStatement.executeUpdate();
        } catch (Exception e) {
            System.out.println("删除班车异常");
            e.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
        if (successNum > 0) {
            return true;
        } else {
            return false;
        }
    }
}
